package projects;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;

@SuppressWarnings("serial")
public class milestone implements Serializable{
	private Integer milestoneId;
	private Integer projectId;
	private Integer milestoneStateId;
	private String milestoneStateReason;
	private Date startDate;
	private Date endDate;
	private String milestoneName;
	private String milestoneDescription;
	private Double milestoneEffort;
	private Double estimatedCost;
	private Double actualCost;
	
	public Double getEstimatedCost() {
		return estimatedCost;
	}

	public void setEstimatedCost(Double estimatedCost) {
		this.estimatedCost = estimatedCost;
	}

	public Double getActualCost() {
		return actualCost;
	}

	public void setActualCost(Double actualCost) {
		this.actualCost = actualCost;
	}

	public Integer getMilestoneId() {
		return milestoneId;
	}
	
	public void setMilestoneId(Integer milestoneId) {
		this.milestoneId = milestoneId;
	}
	
	public Integer getProjectId() {
		return projectId;
	}
	
	public void setProjectId(Integer projectId) {
		this.projectId = projectId;
	}
	
	public Integer getMilestoneStateId() {
		return milestoneStateId;
	}

	public void setMilestoneStateId(Integer milestoneStateId) {
		this.milestoneStateId = milestoneStateId;
	}

	public String getMilestoneStateReason() {
		return milestoneStateReason;
	}

	public void setMilestoneStateReason(String milestoneStateReason) {
		this.milestoneStateReason = milestoneStateReason;
	}

	public String getStartDate() {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		return format.format(startDate);
	}
	
	public void setStartDate(String startDate) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		this.startDate = format.parse(startDate);
	}
	
	public String getEndDate() {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		return format.format(endDate);
	}
	
	public void setEndDate(String endDate) throws ParseException {
		SimpleDateFormat format = new SimpleDateFormat("yyyy-mm-dd");
		this.endDate = format.parse(endDate);
	}
	
	public String getMilestoneName() {
		return milestoneName;
	}
	
	public void setMilestoneName(String milestoneName) {
		this.milestoneName = milestoneName;
	}
	
	public String getMilestoneDescription() {
		return milestoneDescription;
	}
	
	public void setMilestoneDescription(String milestoneDescription) {
		this.milestoneDescription = milestoneDescription;
	}
	
	public Double getMilestoneEffort() {
		return milestoneEffort;
	}
	
	public void setMilestoneEffort(Double milestoneEffort) {
		this.milestoneEffort = milestoneEffort;
	}

//------------------------- create's a new milestone in a project ---------------------------------	
	
	public Integer createMilestone(){
		Connection con = null;
		Integer milestoneId = 0;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement insertMilestone = (PreparedStatement) con.prepareStatement("insert into milestones(projectId, milestoneStateId, milestoneStateReason, milestoneName, milestoneDescription, startDate, endDate, milestoneEffort, estimatedCost) values(?, ?, ?, ?, ?, ?, ?, ?, ?)", PreparedStatement.RETURN_GENERATED_KEYS);
			insertMilestone.setInt(1, this.getProjectId());
			insertMilestone.setInt(2, this.getMilestoneStateId());
			insertMilestone.setString(3, this.getMilestoneStateReason());
			insertMilestone.setString(4, this.getMilestoneName());
			insertMilestone.setString(5, this.getMilestoneDescription());
			insertMilestone.setString(6, this.getStartDate());
			insertMilestone.setString(7, this.getEndDate());
			insertMilestone.setDouble(8, this.getMilestoneEffort());
			insertMilestone.setDouble(9, this.getEstimatedCost());
			Integer result = insertMilestone.executeUpdate();
			
			if (result == 1){
				ResultSet rs = insertMilestone.getGeneratedKeys();
			
				while(rs.next()){
					milestoneId = rs.getInt(1);
				}
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return milestoneId;
	}
	
//------------------------ update's the specific information of a project -------------------------
	
	public boolean updateMilestone(){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateMilestone = (PreparedStatement) con.prepareStatement("update milestones set startDate = ?, endDate = ?, milestoneName = ?, milestoneDescription = ?, milestoneEffort = ?, estimatedCost = ? where milestoneId = ?");
			updateMilestone.setString(1, this.getStartDate());
			updateMilestone.setString(2, this.getEndDate());
			updateMilestone.setString(3, this.getMilestoneName());
			updateMilestone.setString(4, this.getMilestoneDescription());
			updateMilestone.setDouble(5, this.getMilestoneEffort());
			updateMilestone.setDouble(6, this.getEstimatedCost());
			updateMilestone.setInt(7, this.getMilestoneId());
			Integer result = updateMilestone.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
				return isExecuted;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//----------------------- show's all the milestones of a specific projectId -----------------------	
	
	@SuppressWarnings("rawtypes")
	public Hashtable queryMilestonesByProjectId(){
		Connection con = null;
		Hashtable<Integer, ArrayList<String>> info = new Hashtable<Integer, ArrayList<String>>();
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement queryMilestones = (PreparedStatement) con.prepareStatement("select ml.milestoneId, ml.milestoneName, st.milestoneStateName, datediff(endDate, sysdate()) from milestones ml, milestoneStates st where ml.milestoneStateId = st.milestoneStateId and ml.projectId = ? order by ml.startDate");
			queryMilestones.setInt(1, this.getProjectId());
			ResultSet rs = queryMilestones.executeQuery();
			
			while (rs.next()){
				ArrayList<String> milestoneInfo = new ArrayList<String>();
				milestoneInfo.add(rs.getString(2));					//--> add to array list milstoneName
				milestoneInfo.add(rs.getString(3));					//--> add to array list stateName
				milestoneInfo.add(rs.getString(4));					//--> add to array list days till deadline
				info.put(rs.getInt(1), milestoneInfo); 
			}
			return info;
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;	
	}

//---------------------- removes a milestone from a project ---------------------------------------	
	
	public boolean removeMilestone(){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateProject = (PreparedStatement) con.prepareStatement("delete from milestones where milestoneId = ?");
			updateProject.setInt(1, this.getMilestoneId());
			Integer result = updateProject.executeUpdate();
			
			if (result == 1){
				 isExecuted = true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
//--------------------- change's the state and state reason of a specific milestone ---------------	
	
	public boolean updateMilestoneState(){
		Connection con = null;
		boolean isExecuted = false;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement updateMilestone = (PreparedStatement) con.prepareStatement("update milestones set milestoneStateId = ?, milestoneStateReason = ? where milestoneId = ?");
			updateMilestone.setInt(1, this.getMilestoneStateId());
			updateMilestone.setString(2, this.getMilestoneStateReason());
			updateMilestone.setInt(3, this.getMilestoneId());
			Integer result = updateMilestone.executeUpdate();
			
			if (result == 1){
				isExecuted = true;
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return isExecuted;
	}
	
	@SuppressWarnings("rawtypes")
	public ArrayList queryMilestoneInfo(){
		Connection con = null;
		ArrayList<String> info = new ArrayList<String>();  							//CNPI - code-name-ptojectId
		
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement query = (PreparedStatement) con.prepareStatement("select mil.milestoneId, mil.milestoneName, mil.milestoneDescription, mil.milestoneEffort, s.milestoneStateName, mil.milestoneStateReason, mil.estimatedCost, mil.startDate, mil.endDate from milestones mil,milestoneStates s where s.milestoneStateId = mil.milestoneStateId and mil.milestoneId = ?");
			query.setInt(1, this.getMilestoneId());
			ResultSet rs = query.executeQuery();
			
			while(rs.next()){
				info.add(rs.getString(1));						  //    am adaugat in pe 1 pozitie  milestone Id'ul
				info.add(rs.getString(2));                        // 1. am adaugat in array list milestone name
				info.add(rs.getString(3));						  // 2. am adaugat in array list milestone description	
				info.add(rs.getString(4));						  // 3. am adaugat in array list milestone effort
				info.add(rs.getString(5));						  // 3. am adaugat in array list milestone state name
				info.add(rs.getString(6));						  // 3. am adaugat in array list milestone state reason
				info.add(rs.getString(7));						  // 3. am adaugat in array list milestone estimated cost
				info.add(rs.getString(8));						  // 3. am adaugat in array list milestone start date
				info.add(rs.getString(9));						  // 3. am adaugat in array list milestone end date
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return info;
	}
	
	public Integer queryProjectId(){
		Connection con = null;
		Integer projectId = 0;
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:8889/projectsDb","root", "root");
			
			PreparedStatement queryEmployee = (PreparedStatement) con.prepareStatement("select projectId from milestones where milestoneId = ?");
			queryEmployee.setInt(1, this.getMilestoneId());
			ResultSet rs = queryEmployee.executeQuery();
			
			while (rs.next()){
				projectId = rs.getInt(1);
			}
		}
		catch (Exception e){
			System.out.println(e.getMessage());
		}
		finally{
			try {
				if(con != null)
					con.close();
			} 
			catch(Exception e) {}
		}
		return projectId;
	}
	
	public static void main(String args[]) throws ParseException{
		milestone x = new milestone();
//		x.setProjectId(3);
//		x.setMilestoneStateId(1);
//		x.setMilestoneStateReason("milestoneStateReason33 test");
//		x.setMilestoneName("milestoneName33 test");
//		x.setMilestoneDescription("milestoneDescription33 test");
//		x.setStartDate("2011-07-10");
//		x.setEndDate("2012-08-11");
//		x.setMilestoneEffort(11.1);
//		x.setEstimatedCost(1.0);
		x.setMilestoneId(5);
		System.out.println(x.queryProjectId());
		
	}
}
